import config


def create_mssql_insert_trigger(dir_path):
    mysql_cols = ""  # cus_no,name,snm

    sql_server_cols_arg = ""  # @cus_no nvarchar(200),@name nvarchar(200),@snm nvarchar(200)
    sql_server_cols_val = ""  # @cus_no,@name,@snm
    sql_server_cols_get_val = ""  # @cus_no=cus_no,@name=name,@snm = snm

    # dir_path = "C:/APS/export/mapping/cust/mssql4cust2bak_cust"
    tar_dir = dir_path.split("/")[-1]
    export_file_name = tar_dir + "_insert"
    op_db_type = tar_dir.split("4")[0]
    op_db_dbs = tar_dir.split("4")[1]
    sql_server_table_name = op_db_dbs.split("2")[0]
    mysql_table_name = op_db_dbs.split("2")[1]

    read_mysql_file = open(dir_path + "/" + mysql_table_name, 'r')
    read_sql_server_file = open(dir_path + "/" + sql_server_table_name, 'r')
    try:
        while True:
            line = read_mysql_file.readline().strip('\n')
            if (len(line.strip()) == 0):
                break
            line_elems = line.split(" ")
            mysql_cols = mysql_cols + line_elems[0] + ","
    finally:
        read_mysql_file.close()

    try:
        while True:
            line = read_sql_server_file.readline().strip('\n')
            if (len(line.strip()) == 0):
                break
            line_elems = line.split(" ")
            sql_server_cols_arg = sql_server_cols_arg + "@" + line_elems[0] + " " + line_elems[1] + ","
            sql_server_cols_val = sql_server_cols_val + "@" + line_elems[0] + ","
            sql_server_cols_get_val = sql_server_cols_get_val + "@" + line_elems[0] + "=" + line_elems[0] + ","

    finally:
        read_sql_server_file.close()

    mysql_cols = mysql_cols.strip(",")
    sql_server_cols_arg = sql_server_cols_arg.strip(",")
    sql_server_cols_val = sql_server_cols_val.strip(",")
    sql_server_cols_get_val = sql_server_cols_get_val.strip(",")

    # 以下为mssql_insert_trigger的生成模板
    str = f"create PROCEDURE sp_insert_{sql_server_table_name}(\n" \
          f"{sql_server_cols_arg}\n" \
          f")\n" \
          f"AS\n" \
          f"BEGIN\n" \
          f"SET NOCOUNT ON;\n" \
          f"Insert openquery(kaer, 'select * from {mysql_table_name}')({mysql_cols})values({sql_server_cols_val})\n" \
          f"END\n" \
          f"go\n" \
          f"create trigger tr_insert_{sql_server_table_name} on dbo.{sql_server_table_name}\n" \
          f"for insert\n" \
          f"as\n" \
          f"declare {sql_server_cols_arg}\n" \
          f"select {sql_server_cols_get_val} from inserted;\n" \
          f"begin\n" \
          f"exec loopback.{config.mssql_db}.dbo.sp_insert_{sql_server_table_name} {sql_server_cols_val}\n" \
          f"end\n" \
          f"go"

    output = open(dir_path + "/" + export_file_name, 'x')
    output.write(str)


def create_mssql_update_trigger(dir_path):
    mysql_first_col = ""
    mysql_cols = ""

    sql_server_cols_arg = ""
    sql_server_cols_val = ""
    sql_server_cols_get_val = ""
    sql_server_cols_set_val = ""  # name =@name, snm =@snm

    # dir_path = "C:/APS/export/mapping/cust/mssql4cust2bak_cust"
    tar_dir = dir_path.split("/")[-1]
    export_file_name = tar_dir + "_update"
    op_db_type = tar_dir.split("4")[0]
    op_db_dbs = tar_dir.split("4")[1]
    sql_server_table_name = op_db_dbs.split("2")[0]
    mysql_table_name = op_db_dbs.split("2")[1]

    read_mysql_file = open(dir_path + "/" + mysql_table_name, 'r')
    read_sql_server_file = open(dir_path + "/" + sql_server_table_name, 'r')
    mysql_i = 0
    try:
        while True:
            line = read_mysql_file.readline().strip('\n')
            if (len(line.strip()) == 0):
                break
            line_elems = line.split(" ")
            if (mysql_i == 0):
                mysql_first_col = line_elems[0] + "=@" + line_elems[0]
            mysql_cols = mysql_cols + line_elems[0] + ","
            mysql_i = mysql_i + 1
    finally:
        read_mysql_file.close()

    try:
        while True:
            line = read_sql_server_file.readline().strip('\n')
            if (len(line.strip()) == 0):
                break
            line_elems = line.split(" ")
            sql_server_cols_arg = sql_server_cols_arg + "@" + line_elems[0] + " " + line_elems[1] + ","
            sql_server_cols_val = sql_server_cols_val + "@" + line_elems[0] + ","
            sql_server_cols_get_val = sql_server_cols_get_val + "@" + line_elems[0] + "=" + line_elems[0] + ","
            sql_server_cols_set_val = sql_server_cols_set_val + line_elems[0] + "=@" + line_elems[0] + ","

    finally:
        read_sql_server_file.close()

    mysql_cols = mysql_cols.strip(",")
    sql_server_cols_arg = sql_server_cols_arg.strip(",")
    sql_server_cols_val = sql_server_cols_val.strip(",")
    sql_server_cols_get_val = sql_server_cols_get_val.strip(",")
    sql_server_cols_set_val = sql_server_cols_set_val.strip(",")

    # 以下为mssql_insert_trigger的生成模板
    str = f"create PROCEDURE sp_update_{sql_server_table_name}(\n" \
          f"{sql_server_cols_arg}\n" \
          f")\n" \
          f"AS\n" \
          f"BEGIN\n" \
          f"SET NOCOUNT ON;\n" \
          f"UPDATE openquery(kaer, 'select * from {mysql_table_name}')set {sql_server_cols_set_val} where {mysql_first_col} \n" \
          f"SET NOCOUNT OFF\n" \
          f"END;\n" \
          f"go\n" \
          f"create trigger tr_update_{sql_server_table_name} on dbo.{sql_server_table_name}\n" \
          f"for update\n" \
          f"as\n" \
          f"declare {sql_server_cols_arg}\n" \
          f"select {sql_server_cols_get_val} from inserted;\n" \
          f"begin\n" \
          f"exec loopback.{config.mssql_db}.dbo.sp_update_{sql_server_table_name} {sql_server_cols_val}\n" \
          f"end\n" \
          f"go\n"

    output = open(dir_path + "/" + export_file_name, 'x')
    output.write(str)


def create_mssql_delete_trigger(dir_path):
    mysql_first_col_arg = ""
    mysql_first_col_val = ""
    mysql_first_val_col = ""
    mysql_first_arg_type = ""
    mysql_cols = ""

    sql_server_cols_arg = ""
    sql_server_cols_val = ""
    sql_server_cols_get_val = ""
    sql_server_cols_set_val = ""  # name =@name, snm =@snm

    # dir_path = "C:/APS/export/mapping/cust/mssql4cust2bak_cust"
    tar_dir = dir_path.split("/")[-1]
    export_file_name = tar_dir + "_delete"
    op_db_type = tar_dir.split("4")[0]
    op_db_dbs = tar_dir.split("4")[1]
    sql_server_table_name = op_db_dbs.split("2")[0]
    mysql_table_name = op_db_dbs.split("2")[1]

    read_mysql_file = open(dir_path + "/" + mysql_table_name, 'r')
    read_sql_server_file = open(dir_path + "/" + sql_server_table_name, 'r')
    mysql_i = 0
    try:
        while True:
            line = read_mysql_file.readline().strip('\n')
            if (len(line.strip()) == 0):
                break
            line_elems = line.split(" ")
            if (mysql_i == 0):
                mysql_first_col_arg = "@" + line_elems[0]
                mysql_first_col_val = "@" + line_elems[0] + "=" + line_elems[0]
                mysql_first_val_col = line_elems[0] + "=" + "@" + line_elems[0]
                mysql_first_arg_type = "@" + line_elems[0] + " " + line_elems[1]
            mysql_cols = mysql_cols + line_elems[0] + ","
            mysql_i = mysql_i + 1
    finally:
        read_mysql_file.close()

    try:
        while True:
            line = read_sql_server_file.readline().strip('\n')
            if (len(line.strip()) == 0):
                break
            line_elems = line.split(" ")
            sql_server_cols_arg = sql_server_cols_arg + "@" + line_elems[0] + " " + line_elems[1] + ","
            sql_server_cols_val = sql_server_cols_val + "@" + line_elems[0] + ","
            sql_server_cols_get_val = sql_server_cols_get_val + "@" + line_elems[0] + "=" + line_elems[0] + ","
            sql_server_cols_set_val = sql_server_cols_set_val + line_elems[0] + "=@" + line_elems[0] + ","

    finally:
        read_sql_server_file.close()

    mysql_cols = mysql_cols.strip(",")
    sql_server_cols_arg = sql_server_cols_arg.strip(",")
    sql_server_cols_val = sql_server_cols_val.strip(",")
    sql_server_cols_get_val = sql_server_cols_get_val.strip(",")
    sql_server_cols_set_val = sql_server_cols_set_val.strip(",")

    # 以下为mssql_insert_trigger的生成模板
    str = f"create PROCEDURE sp_delete_{sql_server_table_name}(\n" \
          f"{mysql_first_arg_type}\n" \
          f")\n" \
          f"AS\n" \
          f"BEGIN\n" \
          f"SET NOCOUNT ON;\n" \
          f"DELETE openquery(kaer, 'select * from {mysql_table_name}') where {mysql_first_val_col} \n" \
          f"SET NOCOUNT OFF\n" \
          f"END\n" \
          f"go\n" \
          f"create trigger tr_delete_{sql_server_table_name} on dbo.{sql_server_table_name}\n" \
          f"for delete\n" \
          f"as\n" \
          f"declare {mysql_first_arg_type}\n" \
          f"select {mysql_first_col_val} from DELETED;\n" \
          f"begin\n" \
          f"exec loopback.{config.mssql_db}.dbo.sp_delete_{sql_server_table_name} {mysql_first_col_arg}\n" \
          f"end\n" \
          f"go\n"
    output = open(dir_path + "/" + export_file_name, 'x')
    output.write(str)


test_dir = "C:\APS\export\mapping/tf_pos_my\mssql4tf_pos2bak_tf_pos"
test_dir = test_dir.replace("\\", "/")
print(test_dir)
create_mssql_insert_trigger(test_dir)
create_mssql_update_trigger(test_dir)
create_mssql_delete_trigger(test_dir)
